const Service = require('egg').Service;

class ForumService extends Service {
  // 列出所有论坛帖子，包括用户信息
  async list() {
    const { ctx } = this;
     return await ctx.app.mysql.query(`
      SELECT f.*, u.avatar,u.name,major

      FROM forums f
      JOIN users u ON f.user_id = u.id`);
  }

  // 创建论坛帖子
  async create() {
    const { ctx } = this;
    const { user_id, title, content } = ctx.request.body;
    return await ctx.app.mysql.insert('forums', {
      user_id,
      title,
      content
    });
  }

  // 读取单个论坛帖子，包括用户信息
  async read() {
    const { ctx } = this;
    const id = ctx.params.id;
    return await ctx.app.mysql.query(`
      SELECT f.*, u.avatar,u.name,major
      FROM forums f
      JOIN users u ON f.user_id = u.id 
      WHERE f.id = ?`, [id]);
  }

  // 更新论坛帖子
  async update() {
    const { ctx } = this;
    const id = ctx.params.id;
    const { title, content } = ctx.request.body;
    
    return await ctx.app.mysql.update('forums', {
      title,
      content
    }, {
      where: { id }
    });
    
  }

  // 删除论坛帖子
  async delete() {
    const { ctx } = this;
    const id = ctx.params.id;
    return await ctx.app.mysql.delete('forums', { id });
  }

  // 根据用户ID查找帖子，包括用户信息
  async listByUserId() {
    const { ctx } = this;
    const user_id = ctx.params.user_id;
    return await ctx.app.mysql.query(`
      SELECT f.*, u.avatar,u.name,major
      
      FROM forums f
      JOIN users u ON f.user_id = u.id 
      WHERE f.user_id = ?`, [user_id]);
  }
}

module.exports = ForumService;
